package hotel.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import hotel.dao.RoomDao;
import hotel.entity.RoomEntity;

public class RoomDaoImpl implements RoomDao
{
	private static final Logger logger = Logger.getLogger(RoomDaoImpl.class);
	private static final String ERROR = "Error in RoomDaoImpl:";
	
	private static final String GET_ROOM_BY_ROOMCLASS_QUERY = "select id, roomClass, beds, name, surname, userID from rooms where roomClass=?";
	private static final String GET_ROOM_BY_BEDS_QUERY = "select id, roomClass, beds, name, surname, userID from rooms where beds=?";
	private static final String GET_ROOM_BY_ROOMCLASS_AND_BEDS_QUERY = "select id, roomClass, beds, name, surname, userID from rooms where roomClass=? and beds=?";
	private static final String GET_ROOM_BY_ID_QUERY = "select id, roomClass, beds, name, surname, userID from rooms where id=? ";
	private static final String GET_ROOMS_QUERY = "select id, roomClass, beds, name, surname, userID from rooms";
	private static final String INCERT_ROOM = "insert rooms(roomClass, beds) values  (?, ?);";
	private static final String DELETE_ROOM = "delete from rooms where id=?";
	private static final String UPDATE_ROOM = "update rooms r "
									+ "inner join requests re on r.roomClass = re.roomClass and	r.beds = re.beds "
									+ "inner join users u on u.username = re.username "
									+ "set "
									+ "r.name = u.name, "
									+ "r.surname = u.surname, "
									+ "r.userID = u.id "
									+ "where "
									+ "r.id = ? "
									+ "and "
									+ "re.id = ? ";
	private static final String DISMISS_ROOM = "update rooms "
												+ "set "
												+ "name = null, "
												+ "surname = null, "
												+ "userID = null "
												+ "where id = ?;";
									
	private Connection conn;
	
	public RoomDaoImpl(Connection conn)
	{
		this.conn = conn;
	}
	
	@Override
	public List<RoomEntity> findRoomByRoomClass(String roomClass) throws SQLException 
	{
		List<RoomEntity> roomEntities = new ArrayList<RoomEntity>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.GET_ROOM_BY_ROOMCLASS_QUERY);
			stmt.setString(1, roomClass);
			rs = stmt.executeQuery();

			while (rs.next())
			{
				roomEntities.add(new RoomEntity(
							rs.getInt("id"),
							rs.getString("roomClass"),
							rs.getInt("beds"),
							rs.getString("name"),
							rs.getString("surname"),
							rs.getInt("userID")
								));
			}
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (rs != null) 
        	{
        		try 
        		{
        			rs.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        	
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
		
		return roomEntities;
	}

	@Override
	public List<RoomEntity> findRoomByBeds(int beds) throws SQLException 
	{
		List<RoomEntity> roomEntities = new ArrayList<RoomEntity>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.GET_ROOM_BY_BEDS_QUERY);
			stmt.setInt(1, beds);
			rs = stmt.executeQuery();

			while (rs.next())
			{
				roomEntities.add(new RoomEntity(
							rs.getInt("id"),
							rs.getString("roomClass"),
							rs.getInt("beds"),
							rs.getString("name"),
							rs.getString("surname"),
							rs.getInt("userID")
								));
			}
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (rs != null) 
        	{
        		try 
        		{
        			rs.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        	
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
		
		return roomEntities;
	}

	@Override
	public List<RoomEntity> findRoomByRoomClassAndBeds(int beds, String roomClass) throws SQLException 
	{
		List<RoomEntity> roomEntities = new ArrayList<RoomEntity>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.GET_ROOM_BY_ROOMCLASS_AND_BEDS_QUERY);
			stmt.setString(1, roomClass);
			stmt.setInt(2, beds);
			rs = stmt.executeQuery();

			while (rs.next())
			{
				if (rs.getInt("userID") == 0)
						roomEntities.add(new RoomEntity(
									rs.getInt("id"),
									rs.getString("roomClass"),
									rs.getInt("beds"),
									rs.getString("name"),
									rs.getString("surname"),
									rs.getInt("userID")
										));
			}
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (rs != null) 
        	{
        		try 
        		{
        			rs.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        	
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
		
		return roomEntities;
	}

	@Override
	public List<RoomEntity> findAll() throws SQLException
	{
		List<RoomEntity> roomEntities = new ArrayList<RoomEntity>();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.GET_ROOMS_QUERY);
			rs = stmt.executeQuery();

			while (rs.next())
			{
				roomEntities.add(new RoomEntity(
							rs.getInt("id"),
							rs.getString("roomClass"),
							rs.getInt("beds"),
							rs.getString("name"),
							rs.getString("surname"),
							rs.getInt("userID")
								));
			}
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (rs != null) 
        	{
        		try 
        		{
        			rs.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        	
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
		
		return roomEntities;
	}

	
	@Override
	public RoomEntity findById(int id) throws SQLException
{
		PreparedStatement stmt = null;
		ResultSet rs = null;
		RoomEntity roomEntity = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.GET_ROOM_BY_ID_QUERY);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) 
			{
				roomEntity = new RoomEntity(
									rs.getInt("id"),
									rs.getString("roomClass"),
									rs.getInt("beds"),
									rs.getString("name"),
									rs.getString("surname"),
									rs.getInt("userID"));
			}
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (rs != null) 
        	{
        		try 
        		{
        			rs.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        	
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
		
		return roomEntity;
	}

	@Override
	public void insert(RoomEntity entity) throws SQLException 
	{

		PreparedStatement stmt = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.INCERT_ROOM);
			
			stmt.setString(1, entity.getRoomClass());
			stmt.setInt(2, entity.getBeds());
			
			stmt.executeUpdate();
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
	}

	@Override
	public void updateRoom(final int roomId, final int requestId) throws SQLException 
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.UPDATE_ROOM);
			stmt.setInt(1, roomId);
			stmt.setInt(2, requestId);
			
			stmt.executeUpdate();
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
	}

	@Override
	public void delete(int id) throws SQLException 
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.DELETE_ROOM);
			stmt.setInt(1, id);
			stmt.executeUpdate();
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
	
	}

	public void dismissRoom(final int id) throws SQLException
	{
		PreparedStatement stmt = null;
		try
		{
			stmt = conn.prepareStatement(RoomDaoImpl.DISMISS_ROOM);
			stmt.setInt(1, id);
			stmt.executeUpdate();
		} 
		catch (Exception e) 
		{
			throw e;    
        }
		finally
		{
        	if (stmt != null) 
        	{
        		try 
        		{
        			stmt.close();
    			}
        		catch (Exception ignore) 
        		{
        			logger.error(ERROR + ignore);
        		}
        	}
        }
	}
	
	@Override
	public void update(final RoomEntity entity) throws SQLException
	{}
}
